package com.tony.pmp.server.common;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 *
 * @Title: PoiService
 * @Auther: 皮蛋布丁
 * @Date: 2021/08/03/21:15
 * @Description: 通用的到处excel服务
 */
@Service
public class PoiService {

    private static final Logger log = LoggerFactory.getLogger(PoiService.class);

    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    @Autowired
    private Environment environment;


    /**
    * @Description: manageSheet 多sheet页导出
    * @Param: [dataList, headers, sheetName]
    * @return: org.apache.poi.ss.usermodel.Workbook
    * @Author: 皮蛋布丁
    * @Date: 2021/8/3 21:30
    */
    public Workbook manageSheet(List<Map<String,Object>> dataList,String[] headers,String sheetName) {
        final Integer sheetSzie = 1000;
        //final Integer sheetSize = environment.getProperty("poi.product.excel.sheet.size",Integer.class);
        int dataTotal = dataList.size();

        int sheetTotal = (dataTotal % sheetSzie == 0)? dataTotal / sheetSzie : (dataTotal / sheetSzie + 1);
        int start = 0;
        int end = sheetSzie;

        List<Map<String,Object>> subList;
        Workbook workbook = new HSSFWorkbook();

        for (int i = 0; i < sheetTotal; i++) {
            subList =dataList.subList(start,end);
            workbook = this.fillExcelSheetDataV2(subList,headers,sheetName+"_"+(i+1),workbook);

            start += sheetSzie;
            end += sheetSzie;
            if (end >= dataTotal) {
                end = dataTotal;
            }
        }
        return workbook;
    }

    /**
    * @Description: fillExcelSheetDataV2 添加数据到excel的sheet中 V2
    * @Param: [dataList, headers, sheetName, workbook]
    * @return: org.apache.poi.ss.usermodel.Workbook
    * @Author: 皮蛋布丁
    * @Date: 2021/8/3 21:42
    */
    public Workbook fillExcelSheetDataV2(List<Map<String,Object>> dataList,String[] headers,String sheetName,
                                         Workbook workbook) {
        Sheet sheet = workbook.createSheet(sheetName);

        //TODO:创建sheet的第一行数据(excel的表头)
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }

        //TODO:从第二行开始插入真正的数据列表
        int rowIndex = 1;
        Row row;
        Object obj;

        if (dataList != null && dataList.size() > 0) {
            for (Map<String, Object> rowMap : dataList) {
                try {
                    row = sheet.createRow(rowIndex++);

                    //TODO:遍历表头行(每个key->去到实际的value)
                    for (int i = 0; i < headers.length; i++) {
                        obj = rowMap.get(i);
                        if (obj == null) {
                            log.debug("--");
                            row.createCell(i).setCellValue("");
                        } else if (obj instanceof Date) {
                            String tempDate = simpleDateFormat.format((Date) obj);
                            row.createCell(i).setCellValue((tempDate == null)? "" : tempDate);
                        } else {
                            row.createCell(i).setCellValue(String.valueOf(obj));
                        }
                    }
                } catch (Exception e) {
                    log.debug("添加数据到excel的sheet中发生异常：",e.fillInStackTrace());
                }
            }
        }
        return workbook;
    }

    /**
    * @Description: fillExcelSheetData 添加数据到excel的sheet中 V2
    * @Param: [dataList, headers, sheetName]
    * @return: org.apache.poi.ss.usermodel.Workbook
    * @Author: 皮蛋布丁
    * @Date: 2021/8/3 21:56
    */
    public Workbook fillExcelSheetData(List<Map<Integer,Object>> dataList,String[] headers,
                                       String sheetName) {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);

        //TODO:创建sheet的第一行数据(excel的表头)
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }

        //TODO:从第二行开始插入真正的数据列表
        int rowIndex = 1;
        Row row;
        Object obj;

        if (dataList != null && dataList.size() > 0) {
            for (Map<Integer, Object> rowMap : dataList) {
                try {
                    row = sheet.createRow(rowIndex++);

                    //TODO:遍历表头行(每个key->去到实际的value)
                    for (int i = 0; i < headers.length; i++) {
                        obj = rowMap.get(i);

                        if (obj == null) {
                            row.createCell(i).setCellValue("");
                        } else if (obj instanceof Date) {
                            String tempDate = simpleDateFormat.format((Date) obj);
                            row.createCell(i).setCellValue((tempDate == null)? "" : tempDate);
                        } else {
                            row.createCell(i).setCellValue(String.valueOf(obj));
                        }
                    }
                } catch (Exception e) {
                    log.debug("添加数据到excel的sheet中发生异常：",e.fillInStackTrace());
                }
            }
        }
        return workbook;
    }

}
